#ifndef MAINWINDOW_FILLING_H
#define MAINWINDOW_FILLING_H
#include <QVariant>


	QStringList schema = QStringList()

<< "CREATE TABLE priority_dict (\n\
	priority_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	p_name VARCHAR NOT NULL);"

<< "CREATE TABLE status_dict (\n\
	status_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	s_name VARCHAR NOT NULL);"

<< "CREATE TABLE status_hist (\n\
	s_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	status_id INTEGER NOT NULL,\n\
	task_id INTEGER NOT NULL,\n\
	beg DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,\n\
	end DATE,\n\
	CONSTRAINT fk_status_id FOREIGN KEY (status_id)\n\
		REFERENCES status_dict (status_id)\n\
		ON UPDATE CASCADE,\n\
	CONSTRAINT fk_task_id FOREIGN KEY (task_id)\n\
		REFERENCES tasks (task_id)\n\
		ON UPDATE CASCADE);"

<< "CREATE TABLE dict (\n\
	dict_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	name VARCHAR NOT NULL,\n\
	type VARCHAR NOT NULL,\n\
	mandatory INTEGER NOT NULL,\n\
	completion INTEGER NOT NULL);"

<< "CREATE TABLE tasks (\n\
	task_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	priority INTEGER NOT NULL,\n\
	status INTEGER NOT NULL,\n\
	CONSTRAINT fk_priority_id FOREIGN KEY (priority)\n\
		REFERENCES priority_dict (priority_id)\n\
		ON UPDATE CASCADE,\n\
	CONSTRAINT fk_status_id FOREIGN KEY (status)\n\
		REFERENCES status_dict (status_id)\n\
		ON UPDATE CASCADE);"

<< "CREATE TABLE attributes (\n\
	attr_id INTEGER PRIMARY KEY AUTOINCREMENT,\n\
	task_id INTEGER NOT NULL,\n\
	dict_id INTEGER NOT NULL,\n\
	value VARCHAR NOT NULL,\n\
	CONSTRAINT fk_task_id FOREIGN KEY (task_id)\n\
		REFERENCES tasks (task_id)\n\
		ON UPDATE CASCADE,\n\
	CONSTRAINT fk_dict_id FOREIGN KEY (dict_id)\n\
		REFERENCES dict (dict_id)\n\
		ON UPDATE CASCADE);"




/*
-- Triggers for foreign key mapping:
--
--     attributes(dict_id) REFERENCES dict(dict_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey5_insert_referencing BEFORE INSERT ON attributes WHEN\n\
	new.dict_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dict WHERE new.dict_id == dict_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'dict fk constraint failed: foreign key not found');\n\
END;"

<< "CREATE TRIGGER genfkey5_update_referencing BEFORE\n\
	UPDATE OF dict_id ON attributes WHEN\n\
	new.dict_id IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM dict WHERE new.dict_id == dict_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'dict fk constraint failed: foreign key not found');\n\
END;"

<< "CREATE TRIGGER genfkey5_delete_referenced BEFORE DELETE ON dict WHEN\n\
	EXISTS (SELECT 1 FROM attributes WHERE old.dict_id == dict_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'attributes fk constraint failed: child record found');\n\
END;"

<< "CREATE TRIGGER genfkey5_update_referenced AFTER\n\
	UPDATE OF dict_id ON dict WHEN\n\
	EXISTS (SELECT 1 FROM attributes WHERE old.dict_id == dict_id)\n\
BEGIN\n\
	UPDATE attributes SET dict_id = new.dict_id WHERE dict_id = old.dict_id;\n\
END;"

/*
-- Triggers for foreign key mapping:
--
--     attributes(task_id) REFERENCES tasks(task_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey6_insert_referencing BEFORE INSERT ON attributes WHEN\n\
	new.task_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM tasks WHERE new.task_id == task_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'tasks fk constraint failed: foreign key not found');\n\
END;"

<< "CREATE TRIGGER genfkey6_update_referencing BEFORE\n\
	UPDATE OF task_id ON attributes WHEN\n\
	new.task_id IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM tasks WHERE new.task_id == task_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'tasks fk constraint failed: foreign key not found');\n\
END;"

<< "CREATE TRIGGER genfkey6_delete_referenced BEFORE DELETE ON tasks WHEN\n\
	EXISTS (SELECT 1 FROM attributes WHERE old.task_id == task_id)\n\
BEGIN\n\
	DELETE FROM attributes WHERE old.task_id == task_id;\n\
END;"

<< "CREATE TRIGGER genfkey6_update_referenced AFTER\n\
	UPDATE OF task_id ON tasks WHEN\n\
	EXISTS (SELECT 1 FROM attributes WHERE old.task_id == task_id)\n\
BEGIN\n\
	UPDATE attributes SET task_id = new.task_id WHERE task_id = old.task_id;\n\
END;"

/*
-- Triggers for foreign key mapping:
--
--     status_hist(task_id) REFERENCES tasks(task_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON status_hist WHEN\n\
	new.task_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM tasks WHERE new.task_id == task_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: task_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey1_update_referencing BEFORE\n\
	UPDATE OF task_id ON status_hist WHEN\n\
	new.task_id IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM tasks WHERE new.task_id == task_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: task_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON tasks WHEN\n\
	EXISTS (SELECT 1 FROM status_hist WHERE old.task_id == task_id)\n\
BEGIN\n\
	DELETE FROM status_hist WHERE old.task_id == task_id;\n\
END;"

<< "CREATE TRIGGER genfkey1_update_referenced AFTER\n\
	UPDATE OF task_id ON tasks WHEN\n\
	EXISTS (SELECT 1 FROM status_hist WHERE old.task_id == task_id)\n\
BEGIN\n\
	UPDATE status_hist SET task_id = new.task_id WHERE task_id = old.task_id;\n\
END;"

/*
 * Several triggers for auto status history
 * one for auto close prevous hist record
 * one for new on update status in tasks
 * and one for new task creation
 */

<< "CREATE TRIGGER auto_close_prev_status_hist BEFORE INSERT ON status_hist WHEN\n\
	EXISTS (SELECT 1 FROM status_hist WHERE new.task_id == task_id AND end IS NULL)\n\
BEGIN\n\
	UPDATE status_hist set end = new.beg WHERE new.task_id == task_id AND end IS NULL;\n\
END;"

<< "CREATE TRIGGER new_task_new_satus_hist AFTER INSERT ON tasks\n\
BEGIN\n\
	INSERT INTO status_hist (task_id, status_id) values (new.task_id, new.status);\n\
END;"

<< "CREATE TRIGGER changed_status_new_hist AFTER UPDATE OF status ON tasks WHEN\n\
	new.status != old.status\n\
BEGIN\n\
	INSERT INTO status_hist (task_id, status_id) values (new.task_id, new.status);\n\
END;"

/*
-- Triggers for foreign key mapping:
--
--     status_hist(status_id) REFERENCES status_dict(status_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON status_hist WHEN\n\
	new.status_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM status_dict WHERE new.status_id == status_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: status_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey2_update_referencing BEFORE\n\
	UPDATE OF status_id ON status_hist WHEN\n\
	new.status_id IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM status_dict WHERE new.status_id == status_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: status_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON status_dict WHEN\n\
	EXISTS (SELECT 1 FROM status_hist WHERE old.status_id == status_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: can''t delete status, status hist found');\n\
END;"

<< "CREATE TRIGGER genfkey2_update_referenced AFTER\n\
	UPDATE OF status_id ON status_dict WHEN\n\
	EXISTS (SELECT 1 FROM status_hist WHERE old.status_id == status_id)\n\
BEGIN\n\
	UPDATE status_hist SET status_id = new.status_id WHERE status_id = old.status_id;\n\
END;"

/*
-- Triggers for foreign key mapping:
--
--     tasks(status) REFERENCES status_dict(status_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey3_insert_referencing BEFORE INSERT ON tasks WHEN\n\
	new.status IS NOT NULL AND NOT EXISTS (SELECT 1 FROM status_dict WHERE new.status == status_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: status_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey3_update_referencing BEFORE\n\
	UPDATE OF status ON tasks WHEN\n\
	new.status IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM status_dict WHERE new.status == status_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: status_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey3_delete_referenced BEFORE DELETE ON status_dict WHEN\n\
	EXISTS (SELECT 1 FROM tasks WHERE old.status_id == status)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: can''t delete status^ task found');\n\
END;"

<< "CREATE TRIGGER genfkey3_update_referenced AFTER\n\
	UPDATE OF status_id ON status_dict WHEN\n\
	EXISTS (SELECT 1 FROM tasks WHERE old.status_id == status)\n\
BEGIN\n\
	UPDATE tasks SET status = new.status_id WHERE status = old.status_id;\n\
END;"

/*
-- Triggers for foreign key mapping:
--
--     tasks(priority) REFERENCES priority_dict(priority_id)
--     on delete RESTRICT
--     on update CASCADE
--
*/
<< "CREATE TRIGGER genfkey4_insert_referencing BEFORE INSERT ON tasks WHEN\n\
	new.priority IS NOT NULL AND NOT EXISTS (SELECT 1 FROM priority_dict WHERE new.priority == priority_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: priority_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey4_update_referencing BEFORE\n\
	UPDATE OF priority ON tasks WHEN\n\
	new.priority IS NOT NULL AND\n\
	NOT EXISTS (SELECT 1 FROM priority_dict WHERE new.priority == priority_id)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: priority_id not found');\n\
END;"

<< "CREATE TRIGGER genfkey4_delete_referenced BEFORE DELETE ON priority_dict WHEN\n\
	EXISTS (SELECT 1 FROM tasks WHERE old.priority_id == priority)\n\
BEGIN\n\
	SELECT RAISE(ABORT, 'constraint failed: can''t delet priority, child record found');\n\
END;"

<< "CREATE TRIGGER genfkey4_update_referenced AFTER\n\
	UPDATE OF priority_id ON priority_dict WHEN\n\
	EXISTS (SELECT 1 FROM tasks WHERE old.priority_id == priority)\n\
BEGIN\n\
	UPDATE tasks SET priority = new.priority_id WHERE priority = old.priority_id;\n\
END;"
;

	QStringList filling = QStringList()
			      << "INSERT INTO status_dict (s_name)\n\
			      SELECT 'new'\n\
			      UNION ALL\n\
			      SELECT 'accepted'\n\
			      UNION ALL\n\
			      SELECT 'started'\n\
			      UNION ALL\n\
			      SELECT 'finished'\n\
			      UNION ALL\n\
			      SELECT 'closed';"

			      << "INSERT INTO priority_dict (p_name)\n\
			      SELECT 'lowest'\n\
			      UNION ALL\n\
			      SELECT 'low'\n\
			      UNION ALL\n\
			      SELECT 'medium'\n\
			      UNION ALL\n\
			      SELECT 'high'\n\
			      UNION ALL\n\
			      SELECT 'highest'\n\
			      UNION ALL\n\
			      SELECT 'critical';"

#define QV_type(name) QString::number(QVariant::name)

			      << "INSERT INTO dict (name, type, mandatory, completion)\n\
			      SELECT 'originator', "+QV_type(String)+", 1, 1\n\
			      UNION ALL\n\
			      SELECT 'conductor', "+QV_type(String)+", 0, 1\n\
			      UNION ALL\n\
			      SELECT 'manhours', "+QV_type(Time)+", 1, 0\n\
			      UNION ALL\n\
			      SELECT 'date_sent', "+QV_type(DateTime)+", 1, 0\n\
			      UNION ALL\n\
			      SELECT 'description', "+QV_type(String)+", 1, 0;"
			      ;
#undef QV_type


bool MainWindow::fillDb() {
	QSqlQuery q(dbh);
	foreach (QString stm, schema) {
		if (!q.exec(stm)) {
			qWarning() << "Can't create schema";
			qWarning() << stm;
			qWarning() << q.lastError();
			return false;
		}
	}
	if (!dbh.transaction()) {
		qWarning() << "Can't start transaction:" << dbh.lastError();
		return false;
	}
	foreach (QString stm, filling) {
		if ( !q.exec(stm) ) {
			qWarning() << stm;
			qWarning() << "Can't execute statement:" << q.lastError();
			dbh.rollback();
			return false;
		}
	}
	if ( !dbh.commit() ) {
		qWarning() << "Can't commit filling:" << dbh.lastError();
		return false;
	}
	return true;
}




#endif // MAINWINDOW_FILLING_H
